package a2dp.vol.chat;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class ServiceDB {
	private static final String DATABASE_NAME = "services.db";
	private static final int DATABASE_VERSION = 1;
	private static final String SERVICES_TABLE = "services";
	private static final String ROSTER_TABLE = "roster";
	private Context context;
	private SQLiteDatabase db;
	private SQLiteStatement insertStmt;
	private static final String INSERT = "insert into "
			+ SERVICES_TABLE
			+ "(key, address, port, sname, sasl, tls, reconnect, user, pwd) values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

	public ServiceDB(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();
		this.insertStmt = this.db.compileStatement(INSERT);
	}

	public SQLiteDatabase getDB() {
		return this.db;
	}

	public Vector<ServiceItem> listAll() {
		Vector<ServiceItem> list = new Vector<ServiceItem>();
		Cursor cs = this.db.query(SERVICES_TABLE, new String[] { "key",
				"address", "port", "sname", "sasl", "tls", "reconnect", "user",
				"pwd" }, null, null, null, null, "sname");
		if (cs.moveToFirst()) {
			do{
				ServiceItem si = new ServiceItem();
				si.setKey(cs.getInt(0));
				si.setAddress(cs.getString(1));
				si.setPort(cs.getInt(2));
				si.setSname(cs.getString(3));
				si.setSasl(cs.getInt(4));
				si.setTls(cs.getInt(5));
				si.setReconnect(cs.getInt(6));
				si.setUser(cs.getString(7));
				si.setPwd(cs.getString(8));
				list.add(si);
			}while(cs.moveToNext());
		}
		if(cs != null && !cs.isClosed())
			cs.close();
		
		return list;
	}

	/**
	 * @param serviceName
	 *            is the service to remove from the database by address
	 */
	public void deleteService(String serviceName) {
		this.db.delete(SERVICES_TABLE, "address='" + serviceName + "'", null);
	}

	public long insert(ServiceItem si) {
		long rtn = -1;
		this.insertStmt.bindLong(1, getNewKey()); // assign a new index key
		this.insertStmt.bindString(2, si.getAddress());
		this.insertStmt.bindLong(3, si.getPort());
		this.insertStmt.bindString(4, si.getSname());
		this.insertStmt.bindLong(5, (long) si.getSasl());
		this.insertStmt.bindLong(6, (long) si.getTls());
		this.insertStmt.bindLong(7, (long) si.getReconnect());
		this.insertStmt.bindString(8, si.getUser());
		this.insertStmt.bindString(9, si.getPwd());

		try {
			rtn = this.insertStmt.executeInsert();
		} catch (Exception e) {
			e.printStackTrace();
			rtn = 0;
		}

		return rtn;
	}

	private long getNewKey() {
		try {
			int value = 0;
			Cursor c = db.rawQuery("SELECT max(key) FROM " + SERVICES_TABLE,
					null);
			c.moveToFirst();
			value = c.getInt(0);
			return value + 1;
		} catch (Exception e) {
			return 1;
		}
	}

	public void update(ServiceItem si) {
		ContentValues vals = new ContentValues();
		vals.put("key", si.getKey());
		vals.put("address", si.getAddress());
		vals.put("port", si.getPort());
		vals.put("sname", si.getSname());
		vals.put("sasl", si.getSasl());
		vals.put("tls", si.getTls());
		vals.put("reconnect", si.getReconnect());
		vals.put("user", si.getUser());
		vals.put("pwd", si.getPwd());
		this.db.update(SERVICES_TABLE, vals, "key ='" + si.getKey() + "'", null);
		vals = null;
	}

	public ServiceItem getSI(String addr) {
		ServiceItem si = new ServiceItem();
		Cursor cs = this.db.query(SERVICES_TABLE, null, "address = ?",
				new String[] { addr }, null, null, null);

		try {
			if (cs.moveToFirst()) {
				si.setKey(cs.getInt(0));
				si.setAddress(cs.getString(1));
				si.setPort(cs.getInt(2));
				si.setSname(cs.getString(3));
				si.setSasl(cs.getInt(4));
				si.setTls(cs.getInt(5));
				si.setReconnect(cs.getInt(6));
				si.setUser(cs.getString(7));
				si.setPwd(cs.getString(8));
				cs.close();
			} else
				si = null;
		} catch (Exception e) {
			e.printStackTrace();
			si = null;
		}
		return si;
	}

	public ServiceItem getSI(int key) {
		ServiceItem si = new ServiceItem();
		String skey = Integer.toString(key);
		Cursor cs = this.db.query(SERVICES_TABLE, null, "key = ?",
				new String[] { skey }, null, null, null);

		try {
			if (cs.moveToFirst()) {
				si.setKey(cs.getInt(0));
				si.setAddress(cs.getString(1));
				si.setPort(cs.getInt(2));
				si.setSname(cs.getString(3));
				si.setSasl(cs.getInt(4));
				si.setTls(cs.getInt(5));
				si.setReconnect(cs.getInt(6));
				si.setUser(cs.getString(7));
				si.setPwd(cs.getString(8));
				cs.close();
			} else
				si = null;
		} catch (Exception e) {
			e.printStackTrace();
			si = null;
		}
		return si;
	}

	private static class OpenHelper extends SQLiteOpenHelper {
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE "
					+ SERVICES_TABLE
					+ "(key INTEGER PRIMARY KEY, address TEXT, port INTEGER DEFAULT 433, sname TEXT, sasl INTEGER DEFAULT 1, "
					+ "tls INTEGER DEFAULT 1,reconnect INTEGER DEFAULT 1,user TEXT, pwd TEXT)");

			db.execSQL("CREATE TABLE "
					+ ROSTER_TABLE
					+ " (services_id INTEGER, roster INTEGER PRIMARY KEY, ruser TEXT, readtxt INTEGER DEFAULT 1)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			if ((newVersion < 1 && oldVersion < 1)
					|| (oldVersion > DATABASE_VERSION || newVersion > DATABASE_VERSION)) {
				Log.w("Example",
						"Upgrading database, this will drop tables and recreate.");
				db.execSQL("DROP TABLE IF EXISTS " + SERVICES_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + ROSTER_TABLE);
				onCreate(db);
				return;
			}

			if (newVersion >= 2) {
				Log.w("Update", "Update table and default the new column pname");

				try {

					List<String> columns = GetColumns(db, SERVICES_TABLE);
					db.execSQL("ALTER table " + SERVICES_TABLE
							+ " RENAME TO 'temp_" + SERVICES_TABLE + "'");
					onCreate(db);
					columns.retainAll(GetColumns(db, SERVICES_TABLE));
					String cols = join(columns, ",");
					db.execSQL(String.format(
							"INSERT INTO %s (%s) SELECT %s from temp_%s",
							SERVICES_TABLE, cols, cols, SERVICES_TABLE));
					db.execSQL("DROP table 'temp_" + SERVICES_TABLE + "'");
					return;

				} catch (SQLException e) {
					// if anything goes wrong, just start over
					e.printStackTrace();
					db.execSQL("DROP TABLE IF EXISTS " + SERVICES_TABLE);
					onCreate(db);
				}

			}

		}

		public static List<String> GetColumns(SQLiteDatabase db,
				String tableName) {
			List<String> ar = null;
			Cursor c = null;
			try {
				c = db.rawQuery("select * from " + tableName + " limit 1", null);
				if (c != null) {
					ar = new ArrayList<String>(
							Arrays.asList(c.getColumnNames()));
				}
			} catch (Exception e) {
				Log.v(tableName, e.getMessage(), e);
				e.printStackTrace();
			} finally {
				if (c != null)
					c.close();
			}
			return ar;
		}

		public static String join(List<String> list, String delim) {
			StringBuilder buf = new StringBuilder();
			int num = list.size();
			for (int i = 0; i < num; i++) {
				if (i != 0)
					buf.append(delim);
				buf.append((String) list.get(i));
			}
			return buf.toString();
		}

	}

}
